﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class TrainingLecturersDAL : ITrainingLecturersDAL 
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public DataSet LoadLecturers(Nullable<int> Sequence, string SearchText)
        {
            string strSQL;
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@SearchText", SearchText);

                strSQL = "SELECT tr.*, d.DISTRICT_NAME, a.AMP_NAME, p.PROV_NAME, a.AMP_SEQ, p.PROV_SEQ, tr.email, tr.website"
                    + ", CASE WHEN tr.EMP_SEQ IS NOT NULL THEN CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME)"
                        + " ELSE CONCAT(pr.PREFIX_NAME, tr.FIRST_NAME_LOCAL, '  ', tr.LAST_NAME_LOCAL)"
                        + " END AS FULL_NAME"
                    + " FROM HRM_TRAINERS tr "
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON tr.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES pr ON tr.NAME_PREFIX_SEQ = pr.NAME_PREFIX_SEQ"
                    + " LEFT JOIN MAS_DISTRICTS d ON tr.DISTRICT_SEQ = d.DISTRICT_SEQ"
                    + " LEFT JOIN MAS_AMPHURS a ON d.AMP_SEQ = a.AMP_SEQ"
                    + " LEFT JOIN MAS_PROVINCES p ON a.PROV_SEQ = p.PROV_SEQ"
                    + " WHERE tr.RECORD_STATUS not like @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);                
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return ds;
        }
        public IList<TrainingLecturersInfo> GetFull()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<TrainingLecturersInfo> infoList = new List<TrainingLecturersInfo>();

            try
            { 
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT tr.*"
                  + ",d.district_name, a.amp_name, p.prov_name, a.amp_seq, p.prov_seq"
               + ", case when tr.emp_seq is not null then concat(np.prefix_name, e.first_name, '  ', e.last_name)"
                + " else concat(pr.prefix_name, tr.first_name_local, '  ', tr.last_name_local)"
               + " end as full_name"
                  + ", pr.prefix_name"
                   + " FROM hrm_trainers tr "
               + " left join hrm_ps_employees e on tr.emp_seq = e.emp_seq"
                + " Left Join mas_name_prefixes np ON e.name_prefix_seq = np.name_prefix_seq"
                    + " Left Join mas_name_prefixes pr ON tr.name_prefix_seq = pr.name_prefix_seq"
                  + " LEFT JOIN MAS_DISTRICTS d ON tr.DISTRICT_SEQ = d.DISTRICT_SEQ"
                    + " LEFT JOIN MAS_AMPHURS a ON d.AMP_SEQ = a.AMP_SEQ"
                    + " LEFT JOIN MAS_PROVINCES p ON a.PROV_SEQ = p.PROV_SEQ"
                   + " WHERE tr.record_status not like @delete_status";

               
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    int i = 0;

                    while (i < drc.Count)
                    {
                        TrainingLecturersInfo info = new TrainingLecturersInfo();

                        info.trainer_seq = Convert.ToInt32(drc[i]["trainer_seq"]);
                        info.citizen = drc[i]["citizen"].ToString();
                        info.prefix_name = drc[i]["prefix_name"].ToString();
                        info.first_name_local = drc[i]["first_name_local"].ToString();
                        info.last_name_local = drc[i]["last_name_local"].ToString();
                        info.address = drc[i]["address"].ToString();
                        info.district_seq = cn.ConvertNullInt(drc[i]["district_seq"]);
                        info.prov_seq = cn.ConvertNullInt(drc[i]["prov_seq"]);
                        info.postal_code = drc[i]["postal_code"].ToString();
                        info.tel = drc[i]["tel"].ToString();
                        info.email = drc[i]["email"].ToString();
                        info.website = drc[i]["website"].ToString();
                        info.active = Convert.ToInt32(drc[i]["active"]);
                        info.trainer_name = drc[i]["full_name"].ToString();
                        info.mas_address = drc[i]["address"].ToString() + " " + drc[i]["amp_name"].ToString() + " " + drc[i]["district_name"].ToString() + " " + drc[i]["prov_name"].ToString();
                        info.emp_seq = cn.ConvertNullInt(drc[i]["emp_seq"]);
                        info.amp_seq = cn.ConvertNullInt(drc[i]["amp_seq"]);

                        info.name_prefix_seq = Convert.ToInt32(drc[i]["name_prefix_seq"]);

                        infoList.Add(info);

                        i++;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<TrainingLecturersInfo> GetList()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<TrainingLecturersInfo> infoList = new List<TrainingLecturersInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT pr.name_prefix_seq, pr.prefix_name"
                    + ", tr.trainer_seq, tr.first_name_local"
                    + ", tr.last_name_local"
                    + " FROM hrm_trainers tr"
                    + " Left Join pr ON pr.name_prefix_seq = tr.name_prefix_seq"                    
                    + " WHERE tr.record_status not like @delete_status"
                    + " AND tr.active = '1'";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    int i = 0;

                    while (i < drc.Count)
                    {
                        TrainingLecturersInfo info = new TrainingLecturersInfo();
                        info.name_prefix_seq = Convert.ToInt32(drc[i]["name_prefix_seq"]);
                        info.prefix_name = drc[i]["prefix_name"].ToString();
                        info.trainer_seq = Convert.ToInt32(drc[i]["trainer_seq"]);
                        info.first_name = drc[i]["first_name"].ToString();
                        info.last_name = drc[i]["last_name"].ToString();
                        
                        infoList.Add(info);

                        i++;
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void Insert(TrainingLecturersInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@citizen", info.citizen);
                dbManager.AddParameters(1, "@name_prefix_seq", info.name_prefix_seq);
                dbManager.AddParameters(2, "@first_name_local", info.first_name_local);
                dbManager.AddParameters(3, "@last_name_local", info.last_name_local);
                dbManager.AddParameters(4, "@address", info.address);
                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@district_seq", info.district_seq);
                dbManager.AddParameters(7, "@postal_code", info.postal_code);
                dbManager.AddParameters(8, "@tel", info.tel);
                dbManager.AddParameters(9, "@email", info.email);
                dbManager.AddParameters(10, "@website", info.website);
                dbManager.AddParameters(11, "@active", info.active);
                dbManager.AddParameters(12, "@created_by", info.created_by);
                dbManager.AddParameters(13, "@created_when", DateTime.Now);

                strSQL = "INSERT INTO hrm_trainers"
                    + " (citizen, name_prefix_seq, first_name_local, last_name_local, address, district_seq "
                    + " , postal_code, tel, email, website, active, created_by, created_when,record_status) "
                    + " VALUES (@citizen, @name_prefix_seq, @first_name_local, @last_name_local "
                    + " ,@address,  @district_seq, @postal_code, @tel, @email, @website, @active, @created_by, @created_when, @record_status)";

                dbManager.ExecuteScalar(CommandType.Text, strSQL);
            }
            catch(Exception er)
            {
                throw er;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(TrainingLecturersInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@citizen", info.citizen);
                dbManager.AddParameters(1, "@name_prefix_seq", info.name_prefix_seq);
                dbManager.AddParameters(2, "@first_name_local", info.first_name_local);
                dbManager.AddParameters(3, "@last_name_local", info.last_name_local);
                dbManager.AddParameters(4, "@address", info.address);
                dbManager.AddParameters(5, "@prov_seq", info.prov_seq);
                dbManager.AddParameters(6, "@district_seq", info.district_seq);
                dbManager.AddParameters(7, "@postal_code", info.postal_code);
                dbManager.AddParameters(8, "@tel", info.tel);
                dbManager.AddParameters(9, "@website", info.website);
                dbManager.AddParameters(10, "@active", info.active);
                dbManager.AddParameters(11, "@updated_by", info.updated_by);
                dbManager.AddParameters(12, "@updated_when", DateTime.Now);

                /*
                strSQL = " UPDATE hrm_trainers "
                         + " SET citizen = @citizen,"
                         + " name_prefix_seq = @name_prefix_seq,"
                         + " first_name_local = @ first_name_local,"
                         + " last_name_local = @last_name_local,"
                         + " address = @address,"
                         //+ " prov_seq = @prov_seq,"
                         + " district_seq = @district_seq,"
                         + " postal_code = @postal_code,"
                         + " tel = @tel,"
                         + " email = @email,"
                         + " website = @website,"
                         + " active = @active,"
                         + " updated_by = @updated_by,"
                         + " updated_when = getdate(), "
                         + " WHERE trainer_seq = @trainer_seq ";
                */

                var citizen = string.IsNullOrEmpty(info.citizen) ? "citizen = null" : "citizen ='" + info.citizen + "'";
                var name_prefix_seq = info.name_prefix_seq;
                var first_name_local = string.IsNullOrEmpty(info.first_name_local) ? ",first_name_local = null" : ",first_name_local ='" + info.first_name_local + "'";
                var last_name_local = string.IsNullOrEmpty(info.last_name_local) ? ",last_name_local = null" : ",last_name_local ='" + info.last_name_local + "'";
                var district_seq = info.district_seq == null ? ",district_seq = null" : ",district_seq =" + info.district_seq;
                var postal_code = string.IsNullOrEmpty(info.postal_code) ? ",postal_code = null" : ",postal_code = '" + info.postal_code + "'";
                var tel = string.IsNullOrEmpty(info.tel) ? ",tel = null" : ",tel = '" + info.tel + "'";
                var email = string.IsNullOrEmpty(info.email) ? ",email = null" : ",email ='"+info.email+"'";
                var website = string.IsNullOrEmpty(info.website) ? ",website = null" : ",website = '"+info.website+"'";
                var address = string.IsNullOrEmpty(info.address) ? ",address = null" : ",address = '"+info.address+"'";
                var active =  info.active;
                var updated_by = info.updated_by == null ? ",updated_by = null" : ",updated_by = " + info.updated_by;


                strSQL = " UPDATE hrm_trainers "
                    + " SET " + citizen
                         + " ,name_prefix_seq = " + name_prefix_seq
                         + first_name_local
                         + last_name_local
                         + address
                         + district_seq
                         + postal_code
                         + tel
                         + email
                         + website
                         + " ,active = " + active
                         + updated_by
                         + " ,updated_when = @updated_when"
                         + " WHERE trainer_seq = " + info.trainer_seq  ;
                
                   dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(TrainingLecturersInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@trainer_seq", info.trainer_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE hrm_trainers"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE trainer_seq = @trainer_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
